package com.isyscore.os.metadata.database;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ReflectUtil;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.parser.SqlInfo;
import com.baomidou.mybatisplus.extension.plugins.pagination.DialectFactory;
import com.baomidou.mybatisplus.extension.plugins.pagination.DialectModel;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;
import com.google.common.base.Strings;
import com.isyscore.os.core.exception.DataFactoryException;
import com.isyscore.os.core.exception.ErrorCode;
import com.isyscore.os.core.sqlcore.Builder;
import com.isyscore.os.core.util.ApplicationUtils;
import com.isyscore.os.metadata.constant.CommonConstant;
import com.isyscore.os.metadata.enums.DataSourceTypeEnum;
import com.isyscore.os.metadata.manager.DataSourceManager;
import com.isyscore.os.metadata.model.dto.ColumnIndexDTO;
import com.isyscore.os.metadata.model.dto.DataSourceDTO;
import com.isyscore.os.metadata.model.dto.SqlParseDTO;
import com.isyscore.os.metadata.model.vo.ResultVO;
import com.isyscore.os.metadata.utils.DateUtils;
import com.isyscore.os.metadata.utils.MapVariablesParserUtils;
import com.isyscore.os.metadata.utils.SqlUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.*;
import java.util.function.Consumer;

import static com.isyscore.os.core.exception.ErrorCode.DBTYPE_NOT_SUPPORT_PAGE;

/**
 * @Description :
 * @Author: qkc
 * @Date: 2021/9/28 10:55
 */
@Component
@Slf4j
public abstract class AbstractDatabase implements Database {

    public DataSourceManager dataSourceManager;


    public final Map<String, String> COLUMN_TYPE = new LinkedHashMap<>();
    public final Set<String> DATE_TYPE = new HashSet<>();
    public final Set<String> AGGREGATE_FUNCTION = new HashSet<>();
    public Builder builder;
    public DataSourceTypeEnum dbType;
    public Integer queryTimeOut;

    public Integer getQueryTimeOut() {
        return queryTimeOut;
    }

    public void setQueryTimeOut(Integer queryTimeOut) {
        this.queryTimeOut = queryTimeOut;
    }

    private final JsqlParserCountOptimize countOptimize = new JsqlParserCountOptimize();

    public static final Set<String> DATA_TYPE_TO_STATISTICS_TYPE = new HashSet<>();

    static {
        //mysql
        DATA_TYPE_TO_STATISTICS_TYPE.add("BIGINT");
        DATA_TYPE_TO_STATISTICS_TYPE.add("BIT");
        DATA_TYPE_TO_STATISTICS_TYPE.add("INT");
        DATA_TYPE_TO_STATISTICS_TYPE.add("DECIMAL");
        DATA_TYPE_TO_STATISTICS_TYPE.add("DOUBLE");
        DATA_TYPE_TO_STATISTICS_TYPE.add("FLOAT");

        //clickhouse
        DATA_TYPE_TO_STATISTICS_TYPE.add("INT8");
        DATA_TYPE_TO_STATISTICS_TYPE.add("INT16");
        DATA_TYPE_TO_STATISTICS_TYPE.add("INT32");
        DATA_TYPE_TO_STATISTICS_TYPE.add("INT64");
        DATA_TYPE_TO_STATISTICS_TYPE.add("UINT8");
        DATA_TYPE_TO_STATISTICS_TYPE.add("UINT16");
        DATA_TYPE_TO_STATISTICS_TYPE.add("UINT32");
        DATA_TYPE_TO_STATISTICS_TYPE.add("UINT64");
        DATA_TYPE_TO_STATISTICS_TYPE.add("FLOAT32");
        DATA_TYPE_TO_STATISTICS_TYPE.add("FLOAT64");

        //Oracle
        DATA_TYPE_TO_STATISTICS_TYPE.add("DOUBLE PRECISION");
        DATA_TYPE_TO_STATISTICS_TYPE.add("INTEGER");
        DATA_TYPE_TO_STATISTICS_TYPE.add("LONG");
        DATA_TYPE_TO_STATISTICS_TYPE.add("NUMBER");
        DATA_TYPE_TO_STATISTICS_TYPE.add("NUMBERIC");
        DATA_TYPE_TO_STATISTICS_TYPE.add("SMALLINT");

        //PostgreSQL
        DATA_TYPE_TO_STATISTICS_TYPE.add("INT2");
        DATA_TYPE_TO_STATISTICS_TYPE.add("INT4");
        DATA_TYPE_TO_STATISTICS_TYPE.add("FLOAT4");
        DATA_TYPE_TO_STATISTICS_TYPE.add("FLOAT8");

    }
    @Override
    public DataSourceTypeEnum getDbType() {
        return dbType;
    }

    @Override
    public String escapeTbName(String originTableName, String databaseName) {
        return originTableName;
    }

    @Override
    public String escapeDbName(String databaseName) {
        return databaseName;
    }

    @Override
    public String escapeColName(String originColName) {
        return originColName;
    }

    @Override
    public String escapeColAliasName(String originAliasName) {
        return originAliasName;
    }

    @Override
    public boolean isLegalDateType(String colDataType) {
        if (Strings.isNullOrEmpty(colDataType)) {
            return false;
        }
        return DATE_TYPE.contains(colDataType.toUpperCase());
    }

    @Override
    public Set<String> getLegalDateTypes() {
        return DATE_TYPE;
    }

    @Override
    public Map<String, String> getColumnType() {
        return COLUMN_TYPE;
    }

    /**
     * 执行sql的前置操作
     * 默认为空,供子类重写
     *
     * @param statement
     * @param dataSourceDTO
     * @throws SQLException
     */
    public void afterCreateStatement(Statement statement, DataSourceDTO dataSourceDTO) throws SQLException {

    }

    /**
     * 包装返回的数据
     *
     * @param resultVO
     */
    public void wrapTableData(ResultVO resultVO) {

    }

    public ResultVO execSqlAndGet(DataSourceDTO dataSourceDTO, String sql) {
        List<ColumnIndexDTO> head = new ArrayList<>();
        List<Map<String, Object>> content = new ArrayList<>();
        execSql(dataSourceDTO, sql, statement -> {
            try {
                try (ResultSet rs = statement.executeQuery(sql)) {
                    //获取列
                    ResultSetMetaData md = rs.getMetaData();
                    int columnSize = md.getColumnCount();
                    head(head, md, columnSize);
                    //封装所有记录
                    content(content, rs, md, columnSize);
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        });
        return new ResultVO(head, content);
    }

    public ResultVO execSqlAndGetByParams(DataSourceDTO dataSourceDTO, String sql, Map<String, String> map) {
        List<ColumnIndexDTO> head = new ArrayList<>();
        List<Map<String, Object>> content = new ArrayList<>();
        execSql(dataSourceDTO, sql, map, statement -> {
            try {
                try (ResultSet rs = statement.executeQuery()) {
                    //获取列
                    ResultSetMetaData md = rs.getMetaData();
                    int columnSize = md.getColumnCount();
                    head(head, md, columnSize);
                    //封装所有记录
                    content(content, rs, md, columnSize);
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        });
        return new ResultVO(head, content);
    }

    private void head(List<ColumnIndexDTO> head, ResultSetMetaData md, int columnSize) throws SQLException {
        for (int i = 1; i <= columnSize; i++) {
            ColumnIndexDTO columnIndexDTO = new ColumnIndexDTO();
            columnIndexDTO.setIndex(i);
            columnIndexDTO.setName(md.getColumnLabel(i));
            columnIndexDTO.setColumnName(md.getColumnLabel(i));
            String columnTypeName = md.getColumnTypeName(i);
            columnIndexDTO.setDataType(columnTypeName);
            if (DATA_TYPE_TO_STATISTICS_TYPE.contains(columnTypeName.toUpperCase())) {
                columnIndexDTO.setIsNumber(CommonConstant.IS_NUMBER);
                columnIndexDTO.setStatisticsType(CommonConstant.STATISTICS_TYPE_MEASURE);
            } else {
                columnIndexDTO.setIsNumber(CommonConstant.NOT_NUMBER);
                columnIndexDTO.setStatisticsType(CommonConstant.STATISTICS_TYPE_DIMENSION);
            }
            head.add(columnIndexDTO);
        }
    }

    public void content(List<Map<String, Object>> content, ResultSet rs, ResultSetMetaData md, int columnSize) throws SQLException {
        while (rs.next()) {
            Map<String, Object> value = new HashMap<>();
            for (int i = 1; i <= columnSize; i++) {
                String columnTypeName = md.getColumnTypeName(i);
                Object obj;
                if ("TIMESTAMP".equalsIgnoreCase(columnTypeName) || "DATETIME".equalsIgnoreCase(columnTypeName) || "DATE".equalsIgnoreCase(columnTypeName)) {
                    obj = DateUtils.format(rs.getTimestamp(i), null);
                }
                else if("TEXT".equalsIgnoreCase(columnTypeName)
                        ||"CLOB".equalsIgnoreCase(columnTypeName)
                        ||"BLOB".equalsIgnoreCase(columnTypeName)
                        ||"BINARY".equalsIgnoreCase(columnTypeName)){
                    obj = "该类型不支持展示";
                }
                else {
                    obj = rs.getObject(i);
                }
                value.put(md.getColumnLabel(i), obj);
            }
            content.add(value);
        }
    }

    public void execSql(DataSourceDTO dataSourceDTO, String sql, Consumer<Statement> consumer) {
        if (dataSourceManager == null) {
            dataSourceManager = ApplicationUtils.getBean(DataSourceManager.class);
        }
        try (Connection conn = dataSourceManager.getConn(dataSourceDTO);
             Statement statement = conn.createStatement()) {
            afterCreateStatement(statement, dataSourceDTO);
            //测试连接是否成功时,sql为空
            if (StringUtils.isBlank(sql)) {
                return;
            }
            log.debug("execute DDL SQL : {}", sql);
            //超时设置
            statement.setQueryTimeout(queryTimeOut);
            if (consumer != null) {
                consumer.accept(statement);
            } else {
                statement.execute(sql);
            }
        } catch (Throwable e) {
            log.error("execute sql:{}, error:{}", sql, e.getMessage());
            String message = DataFactoryException.getMessage(e);
            throw new DataFactoryException(ErrorCode.COMMON_ERROR, message);
        }
    }

    public void execSql(DataSourceDTO dataSourceDTO, String sql, Map<String, String> params, Consumer<PreparedStatement> consumer) {
        if (dataSourceManager == null) {
            dataSourceManager = ApplicationUtils.getBean(DataSourceManager.class);
        }
        SqlParseDTO parse = MapVariablesParserUtils.parse(sql, params);
        log.debug("PARSE DDL SQL : {}", parse.getParseSql());
        String parseSql = parse.getParseSql();
        try (Connection conn = dataSourceManager.getConn(dataSourceDTO);
             PreparedStatement statement = conn.prepareStatement(parseSql)) {
            afterCreateStatement(statement, dataSourceDTO);
            //测试连接是否成功时,sql为空
            if (StringUtils.isBlank(parseSql)) {
                return;
            }
            int x = 1;
            Iterator<Map.Entry<String, String>> entries = params.entrySet().iterator();
            while (entries.hasNext()) {
                Map.Entry<String, String> entry = entries.next();
                statement.setString(x, entry.getValue());
                x++;
            }
            //超时设置
            statement.setQueryTimeout(queryTimeOut);
            if (consumer != null) {
                consumer.accept(statement);
            } else {
                statement.execute(parse.getParseSql());
            }
        } catch (Throwable e) {
            log.error("execute sql:{}, error:{}", parseSql, e.getMessage());
            String message = DataFactoryException.getMessage(e);
            throw new DataFactoryException(ErrorCode.COMMON_ERROR, message);
        }
    }

    @Override
    public String countSql(String sql) {
        //使用mp提供的优化类优化count语句，去除count中不必要的字段和join提高性能
        SqlInfo sqlInfo = countOptimize.parser(null, sql);
        return sqlInfo.getSql();
    }

    @Override
    public String pageSql(String sql, int offset, int limit) {
        DbType dbType = DbType.getDbType(this.getDbType().getName());
        if (dbType == null) {
            throw new DataFactoryException(DBTYPE_NOT_SUPPORT_PAGE, this.getDbType().getName());
        }
        //mp返回的sql是带有?的形式，需要进行替换才可以使用
        DialectModel dialectModel = DialectFactory.getDialect(dbType).buildPaginationSql(sql, offset, limit);
        long firstParam = (Long) ReflectUtil.getFieldValue(dialectModel, "firstParam");
        long secondParam = (Long) ReflectUtil.getFieldValue(dialectModel, "secondParam");
        return SqlUtil.replaceSqlPageParam(dialectModel.getDialectSql(), firstParam, secondParam);
    }

    /**
     * <p>
     * 针对于不同特性的数据源类型，可能不能够通过sql查询出来的结果拿来即用，
     * 需要对查询到的结果进行二次处理，逻辑可以写在此方法里
     * </p>
     *
     * @author zhan9yn
     * @date 2021/11/15 10:21 上午
     */
    public void postTableStruct(ResultVO result) {

    }

    /**
     * <p>
     * 用于对展示当前库下面的表做转换
     * </p>
     *
     * @return
     * @throws
     * @author zhan9yn
     * @date 2021/11/15 5:36 下午
     */
    public void postTableList(ResultVO resultVO) {

    }


    boolean isDateTime(String str) {
        try {
            DateTime date = DateUtil.parse(str);
            log.info("是日期类型字符串：[{}]", str);
            return true;
        } catch (Exception e) {
            log.info("不是日期类型字符串：[{}]", str);
            return false;
        }
    }

    @Override
    public String withOutEscapeColName(String escapeColName) {
        return escapeColName;
    }

    @Override
    public String getQuote() {
        return "";
    }

    @Override
    public String withOutEscapeTableName(String escapeTableName) {
        return escapeTableName;
    }

    @Override
    public String copyStructSql(String fromTable, String newTable) {
        String sql = "create table %s as SELECT * FROM %s WHERE 1=2";
        return String.format(sql,newTable,fromTable);
    }
}
